# -*- coding: utf-8 -*-
#### 打卡信息统计，人脸检测并识别计数 ####
# 依赖于ultralytics库
# 作者：Ekaros
# 版本：1.3
# 2025.01.22 v1.0 初始版本(get_faces)
# 2025.01.23 v1.1 增加了 daka 函数，用于提取签到信息
# 2025.01.24 v1.2 增加了 match_face 函数，用于匹配签到信息及图片人脸数量
#                 增加了 insert_daka_data_to_mongodb 函数，用于插入签到信息及图片到数据库
#                 增加了 daka_table 函数，用于将签到信息及图片保存为表格文件
# 2025.01.25 v1.3 优化了 daka_table 函数，用于将签到信息及图片导为压缩包
# 2025.02.10 v1.4 增加了 insert_daka_data_to_table 函数


# 导入ultralytics库，可使用pip install ultralytics安装
from datetime import datetime, timedelta
# from ultralytics import YOLO
import pandas as pd
import numpy as np
import time

# import mail_func
import os
import re

def create_dir(path):
    if not os.path.exists(path):
        os.makedirs(path)

# 签到信息提取函数，返回签到信息
def daka(msg: str) -> list:
    """
    签到信息提取函数，返回签到信息

    :param msg: 签到信息
    """
    if "签到" in msg:
        pattern = r"^(.*?)签到.*?应到(\d+)人.*?实到(\d+)人.*"
    elif "打卡" in msg:
        pattern = r"^(.*?)打卡.*?应到(\d+)人.*?实到(\d+)人.*"
    match = re.search(pattern, msg)

    if match:
        match_list = []
        for i in range(3):
            match_list.append(match.group(i + 1))
        return match_list

# 格式化表格函数
def save_format_excel(
    excel_df: pd.DataFrame,
    img_flag: bool = False,
    excel_path: str = "网格打卡xlsx文件/{}_打卡人数统计表.xlsx".format(
        datetime.now().strftime("%Y-%m-%d")
    ),
) -> bool:
    """
    格式化表格函数

    :param excel_df: 表格数据
    :param img_flag: 是否需要写入图片
    """
    # 定义列名
    if excel_df is None:
        return False
    if not img_flag:
        df = excel_df
        df["打卡时间"] = pd.to_datetime(df["打卡时间"])
        df["打卡时间"] = df["打卡时间"].dt.strftime("%Y-%m-%d %H:%M:%S")
        columns = excel_df.columns.to_list()
        try:
            with pd.ExcelWriter(excel_path, engine="xlsxwriter") as writer:
                df.to_excel(writer, index=False, header=True, sheet_name="当日统计")
                # 获取workbook和worksheet对象
                workbook = writer.book
                worksheet = writer.sheets["当日统计"]
                # 设置列宽
                header_format = workbook.add_format(
                    {
                        "align": "center",
                        "valign": "vcenter",
                        "bg_color": "#DCDCDC",
                        "bold": True,
                        "font_size": 12,
                        "border": 1,
                        "border_color": "#000000",
                    }
                )
                # 手动写入列名并应用格式
                for col_num, col_name in enumerate(df.columns.values):
                    worksheet.write(0, col_num, col_name, header_format)
                # 设置值的列宽和对其方式
                format_align = workbook.add_format(
                    {"align": "center", "valign": "vcenter"}
                )
                widths = [20, 10, 10, 10, 25, 50, 30]
                for col, width in zip(columns, widths):
                    col_idx = columns.index(col)
                    worksheet.set_column(col_idx, col_idx, width, format_align)
            print(f"已保存: {excel_path}")
            return True
        except Exception as e:
            print("保存失败: ", e)
            return False

# 将打卡数据插入现有表格
def insert_daka_data_to_table(
    msg: str,
    Nickname: str,
    img_flag: bool = False,
) -> bool:
    """
    将打卡数据插入现有表格

    :param msg: 签到信息
    :param img_path: 图片路径
    """
    date_day = datetime.now().strftime("%Y-%m-%d")
    daka_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    # 提取签到信息
    match_list = daka(msg)
    if match_list == None:
        print("Error: 签到信息提取失败")
        return None
    # 将签到信息插入表格
    flag = "是" if img_flag else "否"
    excel_header = ["网格", "打卡人", "应到人数", "实到人数", "打卡时间", "原始消息", "是否发送图片"]
    data = [match_list[0], Nickname, match_list[1], match_list[2], daka_time, msg, flag]
    print(data)
    folder_path = "网格打卡xlsx文件/"
    if not os.path.exists(folder_path):
        # 如果不存在，创建文件夹
        try:
            create_dir(folder_path)
        except Exception as e:
            print("创建文件夹失败", e)
    excel_path = folder_path + f"{date_day}_网格打卡人数统计表.xlsx"
    if not os.path.exists(excel_path):
        # 如果不存在，创建表格文件并写入文件
        try:
            # 创建表格
            print("不存在该表，尝试进行创建并保存")
            df = pd.DataFrame(columns=excel_header, data=[data])
            if save_format_excel(excel_df=df, excel_path=excel_path):
                # print(f"已保存: {excel_path}")
                return True
        except Exception as e:
            print("保存失败", e)
            return False
    else:
        # 如果存在，读取表格并写入文件
        df = pd.read_excel(excel_path)
        df["打卡时间"] = pd.to_datetime(df["打卡时间"])
        # 判断一小时内是否打过卡
        daka_time_now = datetime.strptime(daka_time, "%Y-%m-%d %H:%M:%S")
        one_hour_ago = daka_time_now - timedelta(hours=1)
        # print(one_hour_ago)
        # 如果没有打过卡，写入文件
        if not (
            (df["打卡时间"] >= one_hour_ago)
            & (df["打卡时间"] <= daka_time_now)
            & (df["网格"] == match_list[0])
        ).any():
            df.loc[len(df)] = data
            if save_format_excel(excel_df=df, excel_path=excel_path):

                # print(f"打卡数据已保存: {excel_path}")
                return True
        # 一小时内打过卡，则不进行保存
        else:
            print("一小时内已打过卡，不进行保存")
            return False
